package com.xueyi.tenant.supervise.controller;

import com.alibaba.fastjson.JSONArray;
import com.mysql.cj.jdbc.Driver;
import com.xueyi.common.core.web.result.AjaxResult;
import com.xueyi.tenant.api.source.domain.dto.TeSourceDto;
import com.xueyi.tenant.api.source.domain.query.TeSourceQuery;
import com.xueyi.tenant.source.service.ITeSourceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author zlf
 * @version 1.0
 * @data 2023/10/9 8:50
 * 总数据
 */

@RestController
@RequestMapping("/supervise")
public class SupController {


    @Autowired
    private ITeSourceService teSourceService;

    /**
     * 企业数
     */
    @GetMapping("/companyList")
    public AjaxResult companyList(Integer pageNo,Integer pageSize) {
        List list = new ArrayList<>();
        try {
            List<TeSourceDto> sourceDtoList = teSourceService.selectList(new TeSourceQuery());
            for (TeSourceDto teSourceDto : sourceDtoList) {
                Connection connect = connect(teSourceDto);
                // 创建查询语句
                String query = "SELECT * FROM base_company";
                // 创建PreparedStatement对象
                PreparedStatement pstmt = connect.prepareStatement(query);
                // 执行查询
                ResultSet rs = pstmt.executeQuery();
                // 遍历查询结果
                while (rs.next()) {
                    // 获取每一行的数据
                    String id = rs.getString("id");
                    String name = rs.getString("name");
                    String type = rs.getString("type");
                    String logo = rs.getString("logo");
                    String cover = rs.getString("cover");
                    String documentType = rs.getString("document_type");
                    String registrationNum = rs.getString("registration_num");
                    String businessTerm = rs.getString("business_term");
                    String principalProperties = rs.getString("principal_properties");
                    String Industry = rs.getString("Industry");
                    String provinceCode = rs.getString("province_code");
                    String areaIds = rs.getString("area_ids");
                    String cityCode = rs.getString("city_code");
                    String districtCode = rs.getString("district_code");
                    String address = rs.getString("address");
                    String longitude = rs.getString("longitude");
                    String latitude = rs.getString("latitude");
                    String representativeName = rs.getString("representative_name");
                    String representativeType = rs.getString("representative_type");
                    String representativeNum = rs.getString("representative_num");
                    String representativePhone = rs.getString("representative_phone");
                    String contactName = rs.getString("contact_name");
                    String contactCardType = rs.getString("contact_card_type");
                    String contactCard = rs.getString("contact_card");
                    String contactPhone = rs.getString("contact_phone");
                    String contactEmail = rs.getString("contact_email");
                    String licenseInformation = rs.getString("license_information");
                    // 处理数据
                    Map map = new HashMap<>();
                    map.put("id",id);
                    map.put("name",name);
                    map.put("logo",logo);
                    map.put("type",type);
                    map.put("cover",cover);
                    map.put("documentType",documentType);
                    map.put("registrationNum",registrationNum);
                    map.put("businessTerm",businessTerm);
                    map.put("principalProperties",principalProperties);
                    map.put("Industry",Industry);
                    map.put("provinceCode",provinceCode);
                    map.put("areaIds",areaIds);
                    map.put("cityCode",cityCode);
                    map.put("districtCode",districtCode);
                    map.put("address",address);
                    map.put("longitude",longitude);
                    map.put("latitude",latitude);
                    map.put("representativeName",representativeName);
                    map.put("representativeType",representativeType);
                    map.put("representativeNum",representativeNum);
                    map.put("representativePhone",representativePhone);
                    map.put("contactName",contactName);
                    map.put("contactCardType",contactCardType);
                    map.put("contactCard",contactCard);
                    map.put("contactPhone",contactPhone);
                    map.put("contactEmail",contactEmail);
                    map.put("licenseInformation",licenseInformation);
                    list.add(map);
                }
            }
        }catch (Exception e){
            return AjaxResult.error(e+"");
        }
        List pageList = (List) list.stream().skip((pageNo - 1) * pageSize).limit(pageSize).collect(Collectors.toList());
        return AjaxResult.success(pageList);
    }


    /**
     * 企业数
     */
    @GetMapping("/compamyAllList")
    public AjaxResult compamyAllList() {
        List list = new ArrayList<>();
        try {
            List<TeSourceDto> sourceDtoList = teSourceService.selectList(new TeSourceQuery());
            for (TeSourceDto teSourceDto : sourceDtoList) {
                Connection connect = connect(teSourceDto);
                // 创建查询语句
                String query = "SELECT * FROM base_company";
                // 创建PreparedStatement对象
                PreparedStatement pstmt = connect.prepareStatement(query);
                // 执行查询
                ResultSet rs = pstmt.executeQuery();
                // 遍历查询结果
                while (rs.next()) {
                    // 获取每一行的数据
                    String id = rs.getString("id");
                    String name = rs.getString("name");
                    String type = rs.getString("type");
                    String logo = rs.getString("logo");
                    String cover = rs.getString("cover");
                    String documentType = rs.getString("document_type");
                    String registrationNum = rs.getString("registration_num");
                    String businessTerm = rs.getString("business_term");
                    String principalProperties = rs.getString("principal_properties");
                    String Industry = rs.getString("Industry");
                    String provinceCode = rs.getString("province_code");
                    JSONArray areaIds = JSONArray.parseArray(rs.getString("area_ids"));
                    String cityCode = rs.getString("city_code");
                    String districtCode = rs.getString("district_code");
                    String address = rs.getString("address");
                    String longitude = rs.getString("longitude");
                    String latitude = rs.getString("latitude");
                    String representativeName = rs.getString("representative_name");
                    String representativeType = rs.getString("representative_type");
                    String representativeNum = rs.getString("representative_num");
                    String representativePhone = rs.getString("representative_phone");
                    String contactName = rs.getString("contact_name");
                    String contactCardType = rs.getString("contact_card_type");
                    String contactCard = rs.getString("contact_card");
                    String contactPhone = rs.getString("contact_phone");
                    String contactEmail = rs.getString("contact_email");
                    String licenseInformation = rs.getString("license_information");
                    // 处理数据
                    Map map = new HashMap<>();
                    map.put("id",id);
                    map.put("name",name);
                    map.put("logo",logo);
                    map.put("type",type);
                    map.put("cover",cover);
                    map.put("documentType",documentType);
                    map.put("registrationNum",registrationNum);
                    map.put("businessTerm",businessTerm);
                    map.put("principalProperties",principalProperties);
                    map.put("Industry",Industry);
                    map.put("provinceCode",provinceCode);
                    map.put("areaIds",areaIds);
                    map.put("cityCode",cityCode);
                    map.put("districtCode",districtCode);
                    map.put("address",address);
                    map.put("longitude",longitude);
                    map.put("latitude",latitude);
                    map.put("representativeName",representativeName);
                    map.put("representativeType",representativeType);
                    map.put("representativeNum",representativeNum);
                    map.put("representativePhone",representativePhone);
                    map.put("contactName",contactName);
                    map.put("contactCardType",contactCardType);
                    map.put("contactCard",contactCard);
                    map.put("contactPhone",contactPhone);
                    map.put("contactEmail",contactEmail);
                    map.put("licenseInformation",licenseInformation);
                    list.add(map);
                }
            }
        }catch (Exception e){
            return AjaxResult.error(e+"");
        }
       return AjaxResult.success(list);
    }

    /**
     *基地数
     */
    @GetMapping("/areaList")
    public AjaxResult areaList(Integer pageNo,Integer pageSize) {
        List list = new ArrayList<>();
        try {
            List<TeSourceDto> sourceDtoList = teSourceService.selectList(new TeSourceQuery());
            for (TeSourceDto teSourceDto : sourceDtoList) {
                Connection connect = connect(teSourceDto);
                // 创建查询语句
                String query = "SELECT * FROM base_area where del_flag = 0";
                // 创建PreparedStatement对象
                PreparedStatement pstmt = connect.prepareStatement(query);
                // 执行查询
                ResultSet rs = pstmt.executeQuery();
                // 遍历查询结果
                while (rs.next()) {
                    // 获取每一行的数据
                    String id = rs.getString("id");
                    String name = rs.getString("name");
                    String img = rs.getString("img");
                    String code = rs.getString("code");
                    String property = rs.getString("property");
                    String pertain = rs.getString("pertain");
                    JSONArray areaIds = JSONArray.parseArray(rs.getString("area_ids"));
                    String adderss =  rs.getString("adderss");
                    String provinceCode =  rs.getString("province_code");
                    String cityCode =  rs.getString("city_code");
                    String districtCode =  rs.getString("district_code");
                    String longitude =  rs.getString("longitude");
                    String latitude =  rs.getString("latitude");
                    String elevation =  rs.getString("elevation");
                    String area =  rs.getString("area");
                    String baseUser =  rs.getString("base_user");
                    String baseUserName =  rs.getString("base_user_name");
                    String phone =  rs.getString("phone");
                    String content =  rs.getString("content");
                    JSONArray affix = JSONArray.parseArray(rs.getString("affix"));
                    String remark =  rs.getString("remark");
                    String status =  rs.getString("status");
                    // 处理数据
                    Map map = new HashMap<>();
                    map.put("id",id);
                    map.put("name",name);
                    map.put("img",img);
                    map.put("code",code);
                    map.put("property",property);
                    map.put("pertain",pertain);
                    map.put("areaIds",areaIds);
                    map.put("adderss",adderss);
                    map.put("provinceCode",provinceCode);
                    map.put("cityCode",cityCode);
                    map.put("districtCode",districtCode);
                    map.put("longitude",longitude);
                    map.put("latitude",latitude);
                    map.put("elevation",elevation);
                    map.put("area",area);
                    map.put("baseUser",baseUser);
                    map.put("baseUserName",baseUserName);
                    map.put("phone",phone);
                    map.put("affix",affix);
                    map.put("content",content);
                    map.put("remark",remark);
                    map.put("status",status);
                    list.add(map);
                }
            }
        }catch (Exception e){
            return AjaxResult.error(e+"");
        }
        List pageList = (List) list.stream().skip((pageNo - 1) * pageSize).limit(pageSize).collect(Collectors.toList());
        return AjaxResult.success(pageList);
    }


    //方式一，直接导入第三方库驱动类
    private Connection connect(TeSourceDto sourceDto) throws SQLException {

        //获取驱动
        Driver driver = new Driver();
        //获取连接
        String url = sourceDto.getUrlPrepend() +sourceDto.getUrlAppend();
        //将用户名和密码放入到Properities对象中
        Properties properties = new Properties();
        properties.setProperty("user", sourceDto.getUsername());//用户
        properties.setProperty("password", sourceDto.getPassword());//密码
        final Connection connect = driver.connect(url, properties);
        System.out.println("1" + connect);
        return connect;
    }
}
